#region Using

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using System.Globalization;
using BlogEngine.Core;
using BlogEngine.Core.Provider;
using BlogEngine.Core.Provider.Interfaces;

#endregion

namespace BlogEngine.Core.Provider.MSSQL
{
	/// <summary>
	/// Microsoft SQL Server Implementation of BlogProvider
	/// </summary>
	public class CIAlbums : IAlbums
	{
		
		/// <summary>
		/// Gets a Album based on a Guid
		/// </summary>
		/// <param name="id">The album's Guid.</param>
		/// <returns>A matching Album</returns>
		public Album Select(Guid id)
		{
			Album album = new Album();
			SQLManagedConnection myConn = SQLManagedConnection.GetConnection(Provider.DatabaseName);

			try
			{
				myConn.GetTextCommand("SELECT Title, Author, Description, Slug, [Order], IsPublished, DateCreated, DateModified FROM be_Albums WHERE AlbumID = @id");
				myConn.AddInParameter("@id", DbType.Guid, id);

				using (IDataReader reader = myConn.ExecuteReader())
				{
					reader.Read();

					album.Id = id;

					if (!reader.IsDBNull(0))
						album.Title = reader.GetString(0);
					if (!reader.IsDBNull(1))
						album.Author = reader.GetString(1);
					if (!reader.IsDBNull(2))
						album.Description = reader.GetString(2);
					if (!reader.IsDBNull(3))
						album.Slug = reader.GetString(3);
					if (!reader.IsDBNull(4))
						album.Order = reader.GetInt32(4);
					if (!reader.IsDBNull(5))
						album.IsPublished = reader.GetBoolean(5);
					if (!reader.IsDBNull(6))
						album.DateCreated = reader.GetDateTime(6);
					if (!reader.IsDBNull(7))
						album.DateModified = reader.GetDateTime(7);

					reader.Close();
				}
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}

			return album;
		}

		/// <summary>
		/// Inserts a Album
		/// </summary>
		/// <param name="album">Must be a valid Album object.</param>
		public void Insert(Album album)
		{
			if (album == null)
				throw new ArgumentNullException("album");

			SQLManagedConnection myConn = SQLManagedConnection.GetConnection(Provider.DatabaseName);

			try
			{
				myConn.GetTextCommand("INSERT INTO be_Albums (AlbumID, Title, Author, Slug, Description, [Order], IsPublished, DateCreated, DateModified) VALUES (@id, @name, @author, @slug, @desc, @order, @published, @created, @modified)");
				myConn.AddInParameter("@id", DbType.Guid, album.Id);
				myConn.AddInParameter("@name", DbType.String, album.Title);
				myConn.AddInParameter("@author", DbType.String, album.Author);
				myConn.AddInParameter("@slug", DbType.String, album.Slug);
				myConn.AddInParameter("@desc", DbType.String, album.Description);
				myConn.AddInParameter("@order", DbType.Int32, album.Order);
				myConn.AddInParameter("@published", DbType.Boolean, album.IsPublished);
				myConn.AddInParameter("@created", DbType.DateTime, album.DateCreated);
				myConn.AddInParameter("@modified", DbType.DateTime, album.DateModified);

				myConn.ExecuteNonQuery();

				Album.Items.Add(album);
				Album.Items.Sort();
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}
		}

		/// <summary>
		/// Updates a Album
		/// </summary>
		/// <param name="album">Must be a valid Album object.</param>
		public void Update(Album album)
		{
			if (album == null)
			{
				throw new ArgumentNullException("category");
			}

			SQLManagedConnection myConn = SQLManagedConnection.GetConnection(Provider.DatabaseName);
			try
			{
				myConn.GetTextCommand("UPDATE be_Albums " +
				  "SET Title = @name," +
				  "Author = @author," +
				  "Slug = @slug," +
				  "Description = @desc," +
				  "[Order] = @order," +
				  "IsPublished = @published," +
				  "DateCreated = @created," +
				  "DateModified = @modified " +
				  "WHERE AlbumID = @id");

				myConn.AddInParameter("@id", DbType.Guid, album.Id);
				myConn.AddInParameter("@name", DbType.String, album.Title);
				myConn.AddInParameter("@author", DbType.String, album.Author);
				myConn.AddInParameter("@slug", DbType.String, album.Slug);
				myConn.AddInParameter("@desc", DbType.String, album.Description);
				myConn.AddInParameter("@order", DbType.Int32, album.Order);
				myConn.AddInParameter("@published", DbType.Boolean, album.IsPublished);
				myConn.AddInParameter("@created", DbType.DateTime, album.DateCreated);
				myConn.AddInParameter("@modified", DbType.DateTime, album.DateModified);

				myConn.ExecuteNonQuery();

				if (!Album.Items.Contains(album))
					Album.Items.Add(album);

				Album.Items.Sort();
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}

		}

		/// <summary>
		/// Deletes a Album
		/// </summary>
		/// <param name="album">Must be a valid Album object.</param>
		public void Delete(Album album)
		{
			if (album == null)
			{
				throw new ArgumentNullException("album");
			}

			SQLManagedConnection myConn = SQLManagedConnection.GetConnection(Provider.DatabaseName);

			try
			{
				myConn.GetTextCommand("DELETE FROM be_Albums WHERE AlbumID = @id");
				myConn.AddInParameter("@id", DbType.Guid, album.Id);
				myConn.ExecuteNonQuery();

				if (Album.Items.Contains(album))
					Album.Items.Remove(album);

				Album.Items.Sort();
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}
		}

		/// <summary>
		/// Fills an unsorted list of albums.
		/// </summary>
		/// <returns>A List&lt;Album&gt; of all Albums.</returns>
		public List<Album> Fill()
		{
			List<Album> albums = new List<Album>();

			//for each entry found, cause a data load to occur - done this way so the reader is shut first!
			foreach (Guid guid in SQLUtil.GetGuids("SELECT AlbumID FROM be_Albums", Provider.DatabaseName))
				albums.Add(Album.Load(guid));

			albums.Sort();
			return albums;
		}

	}
}
